﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Diagnostics;
using System.DirectoryServices;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;

namespace uTILLIty.ADExtractor.Core.Exporters.Sql
{
	public class SqlExporter : ExporterBase<SqlExporterConfiguration>
	{
		readonly DataTable _providers = DbProviderFactories.GetFactoryClasses();

		public override void Export(QueryInformation query, SearchResultCollection searchResults, SqlExporterConfiguration configuration)
		{
			using (DbConnection connection = CreateConnection(configuration))
			{
				connection.Open();
				DbCommand command = CreateCommand(connection, query, configuration);

				foreach (SearchResult result in searchResults)
				{
					UpdateParameters(command, result, query);
					RunCommand(command, configuration.ContinueOnError);
				}
			}
		}

		private void UpdateParameters(DbCommand command, SearchResult result, QueryInformation query)
		{
			foreach (DbParameter param in command.Parameters)
			{
				var propName = param.ParameterName.Substring(1);
				if (propName.Equals("path", StringComparison.InvariantCultureIgnoreCase))
				{
					param.Value = result.Path;
					continue;
				}
				var prop = query.PropertiesToLoad
					.Single(p => p.DisplayName.Equals(propName, StringComparison.CurrentCultureIgnoreCase));
				param.Value = ActiveDirectoryExtractor.GetValue(result, prop, query) ?? DBNull.Value;
				//in case strings are manipulated in the script
				if (param.Value is string)
					param.Size = 4000;
			}
		}

		private DbCommand CreateCommand(DbConnection connection, QueryInformation query, SqlExporterConfiguration configuration)
		{
			var command = connection.CreateCommand();
			command.CommandText = configuration.CommandText;
			command.CommandType = configuration.CommandType;
			//Identifier specification for SQL-Server used as reference: http://msdn.microsoft.com/en-us/library/ms175874.aspx, excluding further @
			var paramsRegex = new Regex(@"(?in)[^@]\@((?<param>[a-z0-9_#][a-z0-9$#_]*)|[\[""](?<param>[^\]""]+[^\]""]))");
			foreach (Match match in paramsRegex.Matches(command.CommandText))
			{
				var paramName = "@" + match.Groups["param"].Value;
				if (command.Parameters.Contains(paramName))
					continue;

				if (!EnsureParameterSpecified(query, paramName))
					continue;

				var param = command.CreateParameter();
				param.ParameterName = paramName;
				command.Parameters.Add(param);
			}
			return command;
		}

		public bool DontWarnUndeclaredParameters { get; set; }

		private readonly HashSet<string> _warnedParameters = new HashSet<string>();
		protected virtual bool EnsureParameterSpecified(QueryInformation query, string paramName)
		{
			if (paramName.Equals("@path", StringComparison.InvariantCultureIgnoreCase))
				return true; //always supported

			if (query.PropertiesToLoad.Any(p => paramName.Substring(1).Equals(p.DisplayName, StringComparison.InvariantCultureIgnoreCase)))
				return true;

			if (!_warnedParameters.Contains(paramName))
			{
				_warnedParameters.Add(paramName);
				if (!DontWarnUndeclaredParameters)
					RaiseLogMessage(TraceEventType.Warning, Properties.Resources.ParamNotSpecified, paramName);
			}
			return false;
		}

		protected virtual void RunCommand(DbCommand command, bool continueOnError)
		{
			try
			{
				command.ExecuteNonQuery();
			}
			catch (Exception ex)
			{
				var sql = CommandToString(command);
				RaiseLogMessage(new LogEventArgs(TraceEventType.Error, ex.Message) { DebugInformation = sql });
				ex.Data["SQL"] = sql;
				if (!continueOnError)
					throw;
			}
		}

		protected virtual string CommandToString(DbCommand command)
		{
			var sql = new StringBuilder(1024);
			foreach (DbParameter param in command.Parameters)
			{
				string value;
				string typeSuffix = "";
				string dataType = param.DbType.ToString();

				switch (param.DbType)
				{
					case DbType.Date:
					case DbType.DateTime:
					case DbType.DateTime2:
						value = param.Value == null ? "NULL" : string.Format("'{0:o}'", (DateTime)param.Value);
						break;
					case DbType.Int16:
					case DbType.Int32:
					case DbType.Int64:
					case DbType.UInt16:
					case DbType.UInt32:
					case DbType.UInt64:
						value = param.Value == null ? "NULL" : param.Value.ToString();
						break;
					case DbType.Decimal:
					case DbType.Double:
					case DbType.Single:
					case DbType.VarNumeric:
						typeSuffix = "(10,2)";
						value = param.Value == null ? "NULL" : ((decimal)param.Value).ToString("n");
						break;
					//case DbType.Time:
					//case DbType.Guid:
					//case DbType.Object:
					//case DbType.SByte:
					//case DbType.String:
					//case DbType.AnsiStringFixedLength:
					//case DbType.StringFixedLength:
					//case DbType.Xml:
					//case DbType.DateTimeOffset:
					default:
						dataType = "varchar";
						value = param.Value == null ? "NULL" : string.Format("'{0}'", param.Value.ToString().Replace("'", "''"));
						typeSuffix = string.Format("({0})", value.Length);
						break;
				}
				typeSuffix = string.Format(typeSuffix, value.Length);
				sql.AppendFormat("DECLARE {0} {1}{2}; SET {0} = {3}; --DbType: {4}", param.ParameterName, dataType, typeSuffix,
												 value, param.DbType);
				sql.AppendLine();
			}
			sql.AppendLine();
			sql.AppendLine(command.CommandText);
			return sql.ToString();
		}

		protected virtual DbConnection CreateConnection(SqlExporterConfiguration configuration)
		{

			DataRow providerInfos = null;
			foreach (DataRow row in _providers.Rows)
			{
				if (configuration.ProviderName.Equals(row["InvariantName"].ToString(), StringComparison.InvariantCultureIgnoreCase))
					providerInfos = row;
				if (configuration.ProviderName.Equals(row["Name"].ToString(), StringComparison.InvariantCultureIgnoreCase))
					providerInfos = row;
			}
			if (providerInfos == null)
				throw new ConfigurationErrorsException(
					string.Format(Properties.Resources.DbProviderNotFound, configuration.ProviderName,
												GetRegisteredDbProviders()));

			DbProviderFactory factory = DbProviderFactories.GetFactory(providerInfos);

			var connection = factory.CreateConnection();
			if (connection != null)
				connection.ConnectionString = configuration.ConnectionString;

			if (!configuration.SuppressSqlInfoMessages)
				TryAttachToConnectionInfoMessages(connection);

			return connection;
		}

		protected virtual void TryAttachToConnectionInfoMessages(DbConnection connection)
		{
			var sqlConn = connection as SqlConnection;
			if (sqlConn == null)
				return;

			sqlConn.InfoMessage += OnInfoMessageReceived;
		}

		protected virtual void OnInfoMessageReceived(object sender, SqlInfoMessageEventArgs e)
		{
			RaiseLogMessage(TraceEventType.Information, e.Message);
		}

		public virtual string GetRegisteredDbProviders()
		{
			var outText = new StringBuilder();
			outText.AppendLine();
			foreach (DataRow row in _providers.Rows)
			{
				outText.AppendFormat("{0} ({1})", row["Name"], row["InvariantName"]);
				outText.AppendLine();
			}
			return outText.ToString();
		}

	}
}